USE [Weval test]
GO

/****** Object:  StoredProcedure [dbo].[CrnStatistics]    Script Date: 8/14/2013 8:16:47 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[CrnStatistics] @CRN int, @year int, @semester int
AS
BEGIN
	SET NOCOUNT ON;

     --Insert statements for procedure here
	 /*
	 30636
	30663
	30594
	30611
	30673
	30626
	30667
	32975
	32976
	32987
	32981
	32978
	32991
	32985
	33032
	33035
	33037
	33045
	32990
	32974
	32973
	34455
	34664

	 */

--DECLARE @CRN int = 34455, @semester int = 3, @year int = 2013, @testID int = 64529;
DECLARE @testID int = 
(
	SELECT DISTINCT TOP 1 T.ID
	FROM Tests T
		INNER JOIN SectionTests ST ON T.ID = ST.testID
		INNER JOIN Results R ON ST.deliveryID = R.deliveryID
		INNER JOIN CourseSections CS ON R.sectionID = CS.ID
			AND CS.bannerCRN = @CRN
			AND CS.year = @year
			AND CS.semester = @semester
			AND typeID = 5
);

DECLARE @instructorID  int= --Get instructor assigned to class/CRN in question
(
	SELECT DISTINCT TOP 1 ST.instructorID
	FROM SectionsTaught ST 
		INNER JOIN Results R ON R.sectionID = ST.sectionID
			AND (R.deleted = 0 OR R.deleted IS NULL)
		INNER JOIN CourseSections CS ON CS.ID = R.sectionID
			AND CS.bannerCRN = @CRN
			AND CS.year = @year
			AND CS.semester = @semester
);
DECLARE @subjectCode nvarchar(4) =
(
	SELECT TOP 1 subjectcode FROM CourseSections CS WHERE CS.bannerCRN = @CRN AND CS.year = @year AND cs.semester = @semester
);
DECLARE @class nvarchar(20)= --Ex: '1010'
(
	SELECT TOP 1 CS.courseNumber FROM CourseSections CS WHERE CS.bannerCRN = @CRN AND CS.year = @year AND cs.semester = @semester
);

WITH cteMain as
(
	SELECT 
		CASE 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly disagree' THEN 0.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'very poor' THEN 0.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'disagree' THEN 1.0 WHEN LOWER(CAST(D.text AS nvarchar(255)))= 'poor' THEN 1.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'neutral' THEN 2.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'average' THEN 2.0
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'agree' THEN 3.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'good' THEN 3.0 
			WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly agree' THEN 4.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'excellent' THEN 4.0
		END Score,
			QR.sequence,
			bannerCRN,
			year,
			semester,
			CS.subjectCode,
			CS.courseNumber,
			ST.instructorID,
			C.categoryName,
			C.ID
	FROM SectionsTaught ST
			INNER JOIN Results R ON R.sectionID = ST.sectionID
				AND (R.deleted = 0 OR R.deleted IS NULL)
			INNER JOIN CourseSections CS ON CS.ID = R.sectionID
				AND CS.subjectCode = @subjectCode
				--AND CS.bannerCRN = @CRN
				AND CS.year = @year
				AND CS.semester = @semester
			INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
			INNER JOIN Questions Q ON Q.ID = QR.questionID
			INNER JOIN Categories C on C.ID = Q.categoryID
				AND LOWER(C.categoryName) <> 'other' 
				--AND C.ID <> 218695 
				--Do not include other category scores, we don't want questions such as "was the book good?" used
				--for an instructor's overall score.
			INNER JOIN Tests T ON T.ID = Q.testID 
				AND T.typeID = 5 --Course Eval
				AND T.ID = @TestID	
			INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
			INNER JOIN Distractors D ON D.ID = DR.distractorID
			--INNER JOIN Users U ON U.Id = R.studentID
			--INNER JOIN Deliveries DEL ON DEL.ID = R.deliveryID
			INNER JOIN Users UI ON UI.Id = ST.instructorID
),
cteNtile as
(
	SELECT 
		AVG(score) AS InstructorAverage,
		NTILE(10) OVER(ORDER BY AVG(score) ASC) AS DecTile,
		bannerCRN
		FROM 
		cteMain
	GROUP BY
		bannerCRN
),
cteFiveYear AS
(
	SELECT 
 		AVG(score) DepartmentClassFiveYearAverage,
		STDEVP(score) DepartmentClassFiveYearSTDeviation,
		AVG(CASE WHEN instructorID = @instructorID THEN score END) InstructorClassFiveYearAverage,
		STDEVP(CASE WHEN instructorID = @instructorID THEN score END) InstructorClassFiveYearSTDeviation
	FROM
	(
		SELECT 
			CASE 
				WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly disagree' THEN 0.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'very poor' THEN 0.0 
				WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'disagree' THEN 1.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'poor' THEN 1.0 
				WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'neutral' THEN 2.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'average' THEN 2.0
				WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'agree' THEN 3.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'good' THEN 3.0 
				WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly agree' THEN 4.0 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'excellent' THEN 4.0
			END Score,
			ST.instructorID

		
		FROM SectionsTaught ST
				INNER JOIN Results R ON R.sectionID = ST.sectionID
					AND (R.deleted = 0 OR R.deleted IS NULL)
				INNER JOIN CourseSections CS ON CS.ID = R.sectionID
					AND CS.subjectCode = @subjectCode
					AND CS.courseNumber = @class
					--AND CS.bannerCRN = @CRN
					AND CS.year > (@year-6)
				INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
				INNER JOIN Questions Q ON Q.ID = QR.questionID
				INNER JOIN Categories C on C.ID = Q.categoryID
					AND LOWER(C.categoryName) <> 'other' 
				INNER JOIN Tests T ON T.ID = Q.testID 
					AND T.typeID = 5 --Course Eval
					AND T.ID = @TestID
				INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
				INNER JOIN Distractors D ON D.ID = DR.distractorID
				--INNER JOIN Users U ON U.Id = R.studentID
				--INNER JOIN Deliveries DEL ON DEL.ID = R.deliveryID
	) A

)


SELECT 
	AVG(CASE WHEN Q.instructorID = @instructorID THEN score END) InstructorSemesterAverage,
	STDEVP(CASE WHEN Q.instructorID = @instructorID THEN score END) InstructorSemesterSTDeviation,
	AVG(CASE WHEN subjectCode = @subjectCode THEN score END) DepartmentSemesterAverage, 
	STDEVP(CASE WHEN subjectCode = @subjectCode THEN score END) DepartmentSemesterSTDeviation, 
	AVG(CASE WHEN bannerCRN=@CRN AND Q.year = @year AND semester = @semester THEN score END) ClassScore,
	STDEVP(CASE WHEN bannerCRN=@CRN AND Q.year = @year AND semester = @semester THEN score END) ClassSTDeviation,
	(SELECT DecTile FROM cteNtile WHERE bannerCRN = @CRN) DecTile, --JOSH this needed to be changed to point to the crn and not the instructorID
	FiveYear.DepartmentClassFiveYearAverage,
	FiveYear.DepartmentClassFiveYearSTDeviation, 
	FiveYear.InstructorClassFiveYearAverage,
	FiveYear.InstructorClassFiveYearSTDeviation 
FROM
	cteMain Q CROSS JOIN cteFiveYear FiveYear --CROSS JOIN cteNtile AS Decile
GROUP BY 
	FiveYear.DepartmentClassFiveYearAverage,
	FiveYear.DepartmentClassFiveYearSTDeviation, 
	FiveYear.InstructorClassFiveYearAverage,
	FiveYear.InstructorClassFiveYearSTDeviation
END
GO

